/*
	SALES INVOICING INFO FOR PEACHTREE INTEGRATION
	EXEC SP_SALES_INVOICING_INFO
*/
ALTER PROC SP_SALES_INVOICING_INFO AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #RESULTS_TABLE
	(
		[Customer ID] VARCHAR(50),
		[Invoice/CM #] VARCHAR(50),
		[Apply to Invoice Number] VARCHAR(50) DEFAULT '',
		[Credit Memo] VARCHAR(50) DEFAULT 'FALSE',
		[Date] VARCHAR(50),
		[Ship By] VARCHAR(50),
		[Quote] VARCHAR(50) DEFAULT 'FALSE',
		[Quote #] VARCHAR(50) DEFAULT '',
		[Quote Good Thru Date] VARCHAR(50) DEFAULT '',
		[Drop Ship] VARCHAR(50) DEFAULT 'FALSE',
		[Ship to Name] VARCHAR(50),
		[Ship to Address-Line One] VARCHAR(50),
		[Ship to Address-Line Two] VARCHAR(50),
		[Ship to City] VARCHAR(50),
		[Ship to State] VARCHAR(50),
		[Ship to Zipcode] VARCHAR(50),
		[Ship to Country] VARCHAR(50),
		[Customer PO] VARCHAR(50),
		[Ship Via] VARCHAR(50),
		[Ship Date] VARCHAR(50),
		[Date Due] VARCHAR(50) DEFAULT '',
		[Discount Amount] VARCHAR(50) DEFAULT '0',
		[Discount Date] VARCHAR(50) DEFAULT '',
		[Displayed Terms] VARCHAR(50),
		[Sales Representative ID] VARCHAR(50),
		[Accounts Receivable Account] VARCHAR(50) DEFAULT '0',
		[Sales Tax Code] VARCHAR(50),
		[Invoice Note] VARCHAR(500),
		[Note Prints After Line Items] VARCHAR(50) DEFAULT 'FALSE',
		[Statement Note] VARCHAR(50) DEFAULT '',
		[Stmt Note Prints Before Ref] VARCHAR(50) DEFAULT 'FALSE',
		[Internal Note] VARCHAR(50) DEFAULT '',
		[Beginning Balance Transaction] VARCHAR(50) DEFAULT 'FALSE',
		[Number of Distributions] VARCHAR(50),
		[Invoice/CM Distribution] VARCHAR(50),
		[Apply to Invoice Distribution] VARCHAR(50) DEFAULT '',
		[Apply To Sales Order] VARCHAR(50) DEFAULT 'FALSE',
		[Quantity] VARCHAR(50),
		[Stocking Quantity] VARCHAR(50),
		[SO Number] VARCHAR(50) DEFAULT '',
		[Item ID] VARCHAR(50),
		[Serial Number] VARCHAR(50) DEFAULT '',
		[U/M ID] VARCHAR(50),
		[U/M No. of Stocking Units] INT DEFAULT 1,
		[SO Distribution] VARCHAR(50),
		[Description] VARCHAR(500),
		[G/L Account] VARCHAR(50) DEFAULT '',
		[Unit Price] VARCHAR(50),
		[Stocking Unit Price] VARCHAR(50),
		[Tax Type] INT DEFAULT 1,
		[UPC / SKU] VARCHAR(50) DEFAULT '',
		[Weight] VARCHAR(50),
		[Amount] VARCHAR(50),
		[Job ID] VARCHAR(50) DEFAULT '',
		[Sales Tax Authority] VARCHAR(50) DEFAULT '',
		[Transaction Period] VARCHAR(50) DEFAULT '',
		[Transaction Number] VARCHAR(50) DEFAULT '',
		[Return Authorization] VARCHAR(50) DEFAULT '',
		[Voided by Transaction] VARCHAR(50) DEFAULT ''
	)
	
	CREATE TABLE #SALES_INVOICE_INFO_TABLE
	(
		[Customer ID] VARCHAR(50),
		[Invoice/CM #] VARCHAR(50),
		[Date] VARCHAR(50),
		[Ship By] VARCHAR(50),
		[Ship to Name] VARCHAR(50),
		[Ship to Address-Line One] VARCHAR(50),
		[Ship to Address-Line Two] VARCHAR(50),
		[Ship to City] VARCHAR(50),
		[Ship to State] VARCHAR(50),
		[Ship to Zipcode] VARCHAR(50),
		[Ship to Country] VARCHAR(50),
		[Customer PO] VARCHAR(50),
		[Ship Via] VARCHAR(50),
		[Ship Date] VARCHAR(50),
		[Displayed Terms] VARCHAR(50),
		[Sales Representative ID] VARCHAR(50),
		[Sales Tax Code] VARCHAR(50),
		[Invoice Note] VARCHAR(500),
		[Quantity] INT,
		[Item ID] VARCHAR(50),
		[U/M ID] VARCHAR(50),
		[Description] VARCHAR(500),
		[Unit Price] FLOAT,
		[Weight] FLOAT,
		[Amount] FLOAT,
		[TAX_AMOUNT] FLOAT,
		[CSLIT_ID] INT,
		[COOL_LINE_ITEM_NO] INT,
		[SALES_ORDER_NO] VARCHAR(20)
	)
	INSERT INTO #SALES_INVOICE_INFO_TABLE 
	   SELECT C.C_NUMBER,
		CST.INV_NO,
		CONVERT( VARCHAR, CST.CST_SHIP_DATE, 101),
		CONVERT( VARCHAR, COOL.COOL_REQUESTED_DATE, 101),
		SA.ADDRESS_1,
		SA.ADDRESS_2,
		SA.ADDRESS_3,
		SA.CITY AS SHIP_CITY,
		SA.STATE AS SHIP_STATE,
		SA.ZIPCODE AS SHIP_ZIP,
		SA.COUNTRY AS SHIP_COUNTRY,		
		SO.SO_CUSTOMER_PO_NUMBER,
		CST.CST_METHOD,
		CONVERT(VARCHAR, CST.CST_SHIP_DATE,101),
		SO.SHIPPING_TERMS,						/*[Displayed Terms]*/
		SO.SO_SALES_PERSON,						/*[Sales Representative ID]*/
		ISNULL(SO.SO_SALES_TAX_TYPE, ''),		/*[Sales Tax Code]*/
		ISNULL(CST.CST_COMMENTS,''),			/*[Invoice Note]*/
		CSLIT.CSLIT_QTY_SHIPPED,				/*[Quantity]*/	
		P.PART_NUMBER,							/*[Item ID]*/
		ISNULL(CSLIT.CSLIT_UNITS,'') AS UOM,	/*[U/M ID]*/
		ISNULL(CSLIT.CSLIT_DESCRIPTION,''),		/*[Description]*/
		COOL.COOL_UNIT_COST,					/*[Unit Price]*/
		ISNULL(CST.CST_WEIGHT,0),				/*[Weight]*/
		(CSLIT.CSLIT_QTY_SHIPPED*COOL.COOL_UNIT_COST),	/*[Amount]*/
		(CASE WHEN COOL.COOL_TAXABLE = 0 THEN (SELECT (CSLIT.CSLIT_QTY_SHIPPED*COOL.COOL_UNIT_COST*TAX)/100 FROM Sales_Tax_Table WHERE STATE = SO.SO_SALES_TAX_TYPE) ELSE 0 END),
		/*[TAX_AMOUNT]*/
		CSLIT.CSLIT_ID,
		COOL.COOL_LINE_ITEM_NO,
		SO.SALESORDER_NO
			FROM
				CUSTOMER_SHIPPING_TABLE CST
				INNER JOIN CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT ON CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT_STATUS = 'SHIPPED'
				INNER JOIN CUSTOMER_OPEN_ORDER_LINEITEM COOL ON COOL.LINEITEM_ID = CSLIT.LINEITEM_ID
				INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID
				INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
				INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
				INNER JOIN ADDRESS BA ON BA.ADDRESS_ID = CST.CST_BILLING_ADDRESS_ID
				INNER JOIN ADDRESS SA ON SA.ADDRESS_ID = CST.CST_TO_ADDRESS_ID
			WHERE CST.CST_STATUS = 'SHIPPED' AND CST_Invoiced <> 1
	
	DECLARE @CUSTOMER_GENERAL_ACCOUNT VARCHAR(50)
	DECLARE @SO_ACCOUNTS_RECEIVABLE_ACCOUNT VARCHAR(50)
	SELECT @CUSTOMER_GENERAL_ACCOUNT  = ISNULL(CUSTOMER_GENERAL_ACCOUNT, ''), @SO_ACCOUNTS_RECEIVABLE_ACCOUNT = ISNULL(SO_ACCOUNTS_RECEIVABLE_ACCOUNT, '') FROM ACCOUNTS_SETUP_TABLE

	SET @CUSTOMER_GENERAL_ACCOUNT = ISNULL(@CUSTOMER_GENERAL_ACCOUNT,'')
	SET @SO_ACCOUNTS_RECEIVABLE_ACCOUNT = ISNULL(@SO_ACCOUNTS_RECEIVABLE_ACCOUNT,'')
		
	DECLARE @INV_NO VARCHAR(50)
	DECLARE @INV_TAX_AMOUNT FLOAT
	DECLARE @INV_TOT_NO_OF_LINEITEMS INT
	DECLARE @LINEITEM_NO INT
	
	DECLARE @Customer_ID VARCHAR(50)
	DECLARE @Invoice# VARCHAR(50)
	DECLARE @Date VARCHAR(50)
	DECLARE @Ship_By VARCHAR(50)
	DECLARE @Ship_to_Name VARCHAR(50)
	DECLARE @Ship_to_Address_Line_One VARCHAR(50)
	DECLARE @Ship_to_Address_Line_Two VARCHAR(50)
	DECLARE @Ship_to_City VARCHAR(50)
	DECLARE @Ship_to_State VARCHAR(50)
	DECLARE @Ship_to_Zipcode VARCHAR(50)
	DECLARE @Ship_to_Country VARCHAR(50)
	DECLARE @Customer_PO VARCHAR(50)
	DECLARE @Ship_Via VARCHAR(50)
	DECLARE @Ship_Date VARCHAR(50)
	DECLARE @Displayed_Terms VARCHAR(50)
	DECLARE @Sales_Representative_ID VARCHAR(50)
	DECLARE @Sales_Tax_Code VARCHAR(50)
	DECLARE @Invoice_Note VARCHAR(500)
	DECLARE @Quantity INT
	DECLARE @Item_ID VARCHAR(50)
	DECLARE @UOM_ID VARCHAR(50)
	DECLARE @Description VARCHAR(500)
	DECLARE @Unit_Price FLOAT
	DECLARE @Weight FLOAT
	DECLARE @Amount FLOAT
	DECLARE @TAX_AMOUNT FLOAT
	DECLARE @CSLIT_ID INT
	DECLARE @COOL_LINE_ITEM_NO INT
	DECLARE @TOT_LINEITEMS INT
	DECLARE @IDX INT
	DECLARE @SALESORDER_NO VARCHAR(50)
	DECLARE @Apply_To_Sales_Order VARCHAR(50)

	SELECT TOP 1 @INV_NO = [Invoice/CM #] FROM #SALES_INVOICE_INFO_TABLE ORDER BY [Invoice/CM #]
	WHILE @INV_NO IS NOT NULL
	 BEGIN
		SELECT @INV_TOT_NO_OF_LINEITEMS = COUNT(*), @INV_TAX_AMOUNT = SUM([TAX_AMOUNT]) FROM #SALES_INVOICE_INFO_TABLE WHERE [Invoice/CM #] = @INV_NO
		SET @LINEITEM_NO = 1
		SET @TOT_LINEITEMS = @INV_TOT_NO_OF_LINEITEMS
		WHILE ( @LINEITEM_NO <= @INV_TOT_NO_OF_LINEITEMS )
		 BEGIN
			SELECT TOP 1 @Customer_ID =        [Customer ID] ,
				@Invoice# =                [Invoice/CM #] ,
				@Date =                    [Date] ,
				@Ship_By =                 [Ship By] ,
				@Ship_to_Name =            [Ship to Name] ,
				@Ship_to_Address_Line_One =[Ship to Address-Line One] ,
				@Ship_to_Address_Line_Two =[Ship to Address-Line Two] ,
				@Ship_to_City =            [Ship to City] ,
				@Ship_to_State =           [Ship to State] ,
				@Ship_to_Zipcode =         [Ship to Zipcode] ,
				@Ship_to_Country =         [Ship to Country] ,
				@Customer_PO =             [Customer PO] ,
				@Ship_Via =                [Ship Via] ,
				@Ship_Date =               [Ship Date] ,
				@Displayed_Terms =         [Displayed Terms] ,
				@Sales_Representative_ID = [Sales Representative ID] ,
				@Sales_Tax_Code =          [Sales Tax Code] ,
				@Invoice_Note =            [Invoice Note] ,
				@Quantity =                [Quantity] ,
				@Item_ID =                 [Item ID] ,
				@UOM_ID =                  [U/M ID] ,
				@Description =             [Description] ,
				@Unit_Price =              [Unit Price] ,
				@Weight =                  [Weight] ,
				@Amount =                  [Amount] ,
				@TAX_AMOUNT =              [TAX_AMOUNT] ,
				@CSLIT_ID =                [CSLIT_ID] ,
				@COOL_LINE_ITEM_NO =	   [COOL_LINE_ITEM_NO],	
				@SALESORDER_NO = [SALES_ORDER_NO]
			 FROM #SALES_INVOICE_INFO_TABLE WHERE [Invoice/CM #] = @INV_NO 
		
			IF( @SALESORDER_NO IS NOT NULL AND LEN(@SALESORDER_NO) > 0 ) 
				SET @Apply_To_Sales_Order = 'TRUE'
			ELSE
				SET @Apply_To_Sales_Order = 'FALSE'	
			
			SET @IDX= CHARINDEX('   #', @Ship_Via)

			IF ( @IDX = 0 )
			 SET @IDX = LEN(@Ship_Via)
			ELSE
			 SET @IDX = @IDX-1

			SET @Ship_Via = SUBSTRING ( @Ship_Via, 1, @IDX )
			IF ( ISNULL(@INV_TAX_AMOUNT,0) > 0 ) --SALES TAX IS PRESENT SO NEED TO PUT MASTER RECORD
			 BEGIN
				SET @TOT_LINEITEMS = @TOT_LINEITEMS + 1
				INSERT INTO #RESULTS_TABLE ( [Customer ID], [Invoice/CM #], [Date], [Ship By], [Ship to Name], [Ship to Address-Line One], [Ship to Address-Line Two], [Ship to City], [Ship to State],
							[Ship to Zipcode], [Ship to Country], [Customer PO], [Ship Via], [Ship Date], [Displayed Terms], [Sales Representative ID], [Accounts Receivable Account], [Sales Tax Code], [Invoice Note], 
							[Number of Distributions], [Invoice/CM Distribution], [Quantity], [Stocking Quantity], [Item ID], [U/M ID], [SO Distribution], [Description], [G/L Account], [Unit Price], [Stocking Unit Price], [Weight], [Amount], [Sales Tax Authority], [SO Number], [Apply To Sales Order] )
						VALUES ( @Customer_ID, @Invoice#, @Date, @Ship_By, @Ship_to_Name, @Ship_to_Address_Line_One, @Ship_to_Address_Line_Two, @Ship_to_City, @Ship_to_State,
							@Ship_to_Zipcode, @Ship_to_Country, @Customer_PO, @Ship_Via, @Ship_Date, @Displayed_Terms, @Sales_Representative_ID, @SO_ACCOUNTS_RECEIVABLE_ACCOUNT, @Sales_Tax_Code, @Invoice_Note,
							 @TOT_LINEITEMS, 0, 0, 0, '', '', 0, '', @CUSTOMER_GENERAL_ACCOUNT, 0, 0, 0, @INV_TAX_AMOUNT*-1, @Sales_Tax_Code, @SALESORDER_NO, @Apply_To_Sales_Order  )				
				SET @INV_TAX_AMOUNT = 0
			 END
			INSERT INTO #RESULTS_TABLE ( [Customer ID], [Invoice/CM #], [Date], [Ship By], [Ship to Name], [Ship to Address-Line One], [Ship to Address-Line Two], [Ship to City], [Ship to State],
							[Ship to Zipcode], [Ship to Country], [Customer PO], [Ship Via], [Ship Date], [Displayed Terms], [Sales Representative ID], [Accounts Receivable Account], [Sales Tax Code], [Invoice Note], 
							[Number of Distributions], [Invoice/CM Distribution], [Quantity], [Stocking Quantity], [Item ID], [U/M ID], [SO Distribution], [Description], [G/L Account], [Unit Price], [Stocking Unit Price], [Weight], [Amount], [SO Number], [Apply To Sales Order]   )
						VALUES ( @Customer_ID, @Invoice#, @Date, @Ship_By, @Ship_to_Name, @Ship_to_Address_Line_One, @Ship_to_Address_Line_Two, @Ship_to_City, @Ship_to_State,
							@Ship_to_Zipcode, @Ship_to_Country, @Customer_PO, @Ship_Via, @Ship_Date, @Displayed_Terms, @Sales_Representative_ID, @SO_ACCOUNTS_RECEIVABLE_ACCOUNT, @Sales_Tax_Code, @Invoice_Note,
							 @TOT_LINEITEMS, @LINEITEM_NO, @Quantity, @Quantity, @Item_ID, @UOM_ID, @COOL_LINE_ITEM_NO, @Description, @CUSTOMER_GENERAL_ACCOUNT, @Unit_Price, @Unit_Price, @Weight, @Amount*-1, @SALESORDER_NO, @Apply_To_Sales_Order  )				
			SET @LINEITEM_NO = @LINEITEM_NO + 1
			DELETE FROM #SALES_INVOICE_INFO_TABLE WHERE [CSLIT_ID] = @CSLIT_ID

		 END
		
		DELETE FROM #SALES_INVOICE_INFO_TABLE WHERE [Invoice/CM #] = @INV_NO
		
		SET @INV_NO = NULL
		
		SELECT TOP 1 @INV_NO = [Invoice/CM #] FROM #SALES_INVOICE_INFO_TABLE ORDER BY [Invoice/CM #]
	 
	END

	--SELECT TOP 20 * FROM #RESULTS_TABLE
	
	/*SELECT 
		[Customer ID], [Invoice/CM #], [Apply to Invoice Number], [Credit Memo], [Customer PO], [SO Number], [SO Distribution], 
		[Item ID], [Description], [Quantity], [U/M ID], [Unit Price], [Amount], [Ship Via], [Displayed Terms], 	[Ship Date], 
		[Ship to Name], [Ship to Address-Line One], [Ship to Address-Line Two], [Ship to City], [Ship to State], [Ship to Zipcode], 
		[Ship to Country], [Date], [Ship By], [Accounts Receivable Account], [G/L Account], [Quote], [Quote #], [Quote Good Thru Date], 
		[Drop Ship], [Date Due], [Discount Amount], [Discount Date], [Sales Representative ID], [Sales Tax Code], [Invoice Note], 
		[Note Prints After Line Items], [Statement Note], [Stmt Note Prints Before Ref], [Internal Note], [Beginning Balance Transaction], 
		[Number of Distributions], [Invoice/CM Distribution], [Apply to Invoice Distribution], [Apply To Sales Order], [Stocking Quantity], 
		[Serial Number], [U/M No. of Stocking Units], [Stocking Unit Price], [Tax Type], [UPC / SKU], [Weight], [Job ID], [Sales Tax Authority], 
		[Transaction Period], [Transaction Number], [Return Authorization], [Voided by Transaction]
	FROM #RESULTS_TABLE*/

	SELECT
		/*1 */[Customer ID],
		/*2 */[Invoice/CM #],
		/*3 */[Apply to Invoice Number],
		/*4 */[Credit Memo],
		/*5 */[Customer PO],
		/*6 */[SO Number],
		/*7 */[SO Distribution],
		/*8 */[Item ID],
		/*9 */[Description],
		/*10*/[Quantity],
		/*11*/[U/M ID],
		/*12*/[Unit Price],
		/*13*/[Amount],
		/*14*/[Ship Via],
		/*15*/[Displayed Terms],
		/*16*/[Ship Date],
		/*17*/[Ship to Name],
		/*18*/[Ship to Address-Line One],
		/*19*/[Ship to Address-Line Two],
		/*20*/[Ship to City],
		/*21*/[Ship to State],
		/*22*/[Ship to Zipcode],
		/*23*/[Ship to Country],
		/*24*/[Date],
		/*25*/[Ship By],
		/*26*/[Accounts Receivable Account],
		/*27*/[G/L Account],
		/*28*/[Quote],
		/*29*/[Quote #],
		/*30*/[Quote Good Thru Date],
		/*31*/[Drop Ship],
		/*32*/[Date Due],
		/*33*/[Discount Amount],
		/*34*/[Discount Date],
		/*35*/[Sales Representative ID],
		/*36*/[Sales Tax Code],
		/*37*/[Invoice Note],
		/*38*/[Note Prints After Line Items],
		/*39*/[Statement Note],
		/*40*/[Stmt Note Prints Before Ref],
		/*41*/[Internal Note],
		/*42*/[Beginning Balance Transaction],
		/*43*/[Number of Distributions],
		/*44*/[Invoice/CM Distribution],
		/*45*/[Apply to Invoice Distribution],
		/*46*/[Apply To Sales Order],
		/*47*/[Stocking Quantity],
		/*48*/[Serial Number],
		/*49*/[U/M No. of Stocking Units],
		/*50*/[Stocking Unit Price],
		/*51*/[Tax Type],
		/*52*/[UPC / SKU],
		/*53*/[Weight],
		/*54*/[Job ID],
		/*55*/[Sales Tax Authority],
		/*56*/[Transaction Period],
		/*57*/[Transaction Number],
		/*58*/[Return Authorization],
		/*59*/[Voided by Transaction]
	FROM #RESULTS_TABLE

END